package com.ithinkstudio.jeez.generator.db.impl;

import com.ithinkstudio.jeez.generator.config.GenerateConfig;
import com.ithinkstudio.jeez.generator.config.po.TableField;
import com.ithinkstudio.jeez.generator.config.po.TableInfo;
import com.ithinkstudio.jeez.generator.db.IDbQuery;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @program: jeez-springboot-bucket
 * @description: Oracle元数据查询
 * @author: fengzp
 * @create: 2020/05/12
 */
@Service("oracleQuery")
@Slf4j
public class OracleQuery implements IDbQuery {
    @Resource
    private GenerateConfig generateConfig;

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取要生成的表
     *
     * @return
     */
    @Override
    public List<TableInfo> getGenerateTableList() {
        List<TableInfo> tableInfoList = getTableList();
        List<TableField>  fieldList = getTableColumnList();
        for(TableInfo tableInfo : tableInfoList) {
            for(TableField field : fieldList) {
                if(tableInfo.getName().equals(field.getTableName())) {
                    tableInfo.getFields().add(field);
                }
            }
        }

        return tableInfoList;
    }

    /**
     * 获取table列表
     * @return
     */
    private List<TableInfo> getTableList() {
        String tables = generateConfig.getTableNames().stream().map(String::toUpperCase).map(String::trim).collect(Collectors.joining("','"));

        String sql = "select a.table_name," +
                "       b.comments" +
                "  from user_tables a" +
                "  left join user_tab_comments b" +
                "    on a.table_name = b.table_name" +
                " where a.table_name in ('" + tables + "')";
        return jdbcTemplate.query(sql, new RowMapper<TableInfo>() {

            @Override
            public TableInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setComment(rs.getString("COMMENTS"));
                tableInfo.setName(rs.getString("TABLE_NAME"));
                return tableInfo;
            }
        });
    }

    /**
     * 获取table中列信息
     * @return
     */
    private List<TableField> getTableColumnList() {
        String tables = generateConfig.getTableNames().stream().map(String::toUpperCase).map(String::trim).collect(Collectors.joining("','"));

        String sql = "select a.table_name," +
                "   c.column_name," +
                "   c.data_type," +
                "   c.data_length,   " +
                "   d.comments as col_comments" +
                "  from user_tables a" +
                "  left join user_tab_columns c" +
                " on a.table_name = c.table_name" +
                "  left join user_col_comments d" +
                " on c.table_name = d.table_name" +
                "   and c.column_name = d.column_name" +
                " where a.table_name in ('"+tables+"')" +
                " order by a.table_name,c.column_id";
        return jdbcTemplate.query(sql, new RowMapper<TableField>() {

            @Override
            public TableField mapRow(ResultSet rs, int rowNum) throws SQLException {
                TableField tableField = new TableField();
                //表名
                tableField.setTableName(rs.getString("TABLE_NAME"));
                //列注释
                tableField.setComment(rs.getString("COMMENTS"));
                //字段名
                tableField.setColumnName(rs.getString("COLUMN_NAME"));
                //字段类型
                tableField.setType(rs.getString("DATA_TYPE"));
                //字段长度
                tableField.setLength(rs.getInt("DATA_LENGTH"));
                return tableField;
            }
        });
    }


}
